package com.saetool.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

public abstract class AbstractDAO {

	protected Log log = LogFactory.getLog(this.getClass());

	protected JdbcTemplate wJdbcTemplate;

	protected JdbcTemplate rJdbcTemplate;

	protected SimpleJdbcInsert simpleJdbcInsert;

	protected String tableName;

	public abstract void init();

	public abstract RowMapper<?> makeRowMapperInstance();

	public AbstractDAO() {
		init();
	}

	@Resource
	public void setWDataSource(DataSource wDataSource) {
		this.wJdbcTemplate = new JdbcTemplate(wDataSource);
		this.simpleJdbcInsert = new SimpleJdbcInsert(wDataSource).withTableName(tableName).usingGeneratedKeyColumns("id");
	}

	@Resource
	public void setRDataSource(DataSource rDataSource) {
		this.rJdbcTemplate = new JdbcTemplate(rDataSource);
	}

	public String add(Object obj) {
		SqlParameterSource sps = new BeanPropertySqlParameterSource(obj);
		log.debug("准备插入对象：" + obj);
		String id = simpleJdbcInsert.execute(sps);
		log.info("插入成功:" + obj);
		return id;
	}

	public int countTotal() {
		return countTotalFilterBy("");
	}

	public int countTotalFilterBy(String filter, Object... args) {
		String sql = "select count(*) from " + tableName + " " + filter;
		return rJdbcTemplate.queryForInt(sql, args);
	}

	public List<Map<String, Object>> selectAllRows() {
		return rJdbcTemplate.queryForList("select * from " + tableName);
	}

	public void deleteById(String id) {
		String sql = "delete from " + tableName + " where id = ?";
		log.info(sql + "\t" + id);
		int i = rJdbcTemplate.update(sql, id);
		log.info("修改项数：" + i);
	}

	@SuppressWarnings("unchecked")
	public <T> T selectById(String id) {
		try {
			String selectByIdSQL = "select * from " + tableName + " where id = ?";
			return (T) rJdbcTemplate.queryForObject(selectByIdSQL, makeRowMapperInstance(), id);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	};

	@SuppressWarnings("unchecked")
	public <T> List<T> selectAll() {
		try {
			String selectSQL = "select * from " + tableName;
			return (List<T>) rJdbcTemplate.query(selectSQL, makeRowMapperInstance());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	};

	public int deleteBy(String filterStr, Object... args) {
		String sql = "delete from " + tableName + " " + filterStr;
		String argsStr = "";
		for (Object arg : args) {
			argsStr += arg.toString() + ", ";
		}
		log.info(sql + "\t" + argsStr);
		int i = rJdbcTemplate.update(sql, args);
		log.info("修改项数：" + i);
		return i;
	}

	@SuppressWarnings("unchecked")
	public <T> T selectFilterBy(String filterStr, Object... args) {
		String sql = "select * from " + tableName + " " + filterStr;
		String argsStr = "";
		for (Object arg : args) {
			argsStr += arg.toString() + ", ";
		}
		log.debug(sql + "\t" + argsStr);
		try {
			return (T) rJdbcTemplate.queryForObject(sql, makeRowMapperInstance(), args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T> selectFilterByAsObjectList(String filterStr, Object... args) {
		String sql = "select * from " + tableName + " " + filterStr;
		String argsStr = "";
		for (Object arg : args) {
			argsStr += arg.toString() + ", ";
		}
		log.debug(sql + "\t" + argsStr);
		try {
			return (List<T>) rJdbcTemplate.query(sql, makeRowMapperInstance(), args);
		} catch (EmptyResultDataAccessException e) {
			return new ArrayList();
		}
	}

	public List<Map<String, Object>> selectFilterByAsList(String filterStr, Object... args) {
		String sql = "select * from " + tableName + " " + filterStr;
		String argsStr = "";
		for (Object arg : args) {
			argsStr += arg.toString() + ", ";
		}
		log.debug(sql + "\t" + argsStr);
		try {
			return rJdbcTemplate.queryForList(sql, args);
		} catch (EmptyResultDataAccessException e) {
			return new ArrayList<Map<String, Object>>();
		}
	}

	public List<Map<String, Object>> selectByStartAndLimit(Integer start, Integer limit) {
		return selectByStartAndLimitOrderBy(start, limit, "");
	}

	public List<Map<String, Object>> selectByStartAndLimitOrderBy(Integer start, Integer limit, String orderBy) {
		return selectFilterByStartAndLimitOrderBy("", start, limit, orderBy);
	}

	public List<Map<String, Object>> selectFilterByStartAndLimitOrderBy(String filterBy, Integer start, Integer limit, String orderBy, Object... args) {
		Integer end = start + limit;
		String sql = "SELECT * FROM (Select A.*, Rownum Rn FROM (SELECT * FROM " + tableName + " " + filterBy + " " + orderBy + ") A Where Rownum <= ?) WHERE RN > ?";

		Object[] argsWithEndStart = new Object[args.length + 2];
		for (int i = 0; i < args.length; i++) {
			argsWithEndStart[i] = args[i];
		}
		argsWithEndStart[args.length] = end;
		argsWithEndStart[args.length + 1] = start;

		return rJdbcTemplate.queryForList(sql, argsWithEndStart);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T> selectFilterByStartAndLimitOrderByAsObjectList(String filterBy, Integer start, Integer limit, String orderBy, Object... args) {
		Integer end = start + limit;
		String sql = "SELECT * FROM (Select A.*, Rownum Rn FROM (SELECT * FROM " + tableName + " " + filterBy + " " + orderBy + ") A Where Rownum <= ?) WHERE RN > ?";

		Object[] argsWithEndStart = new Object[args.length + 2];
		for (int i = 0; i < args.length; i++) {
			argsWithEndStart[i] = args[i];
		}
		argsWithEndStart[args.length] = end;
		argsWithEndStart[args.length + 1] = start;

		try {
			return (List<T>) rJdbcTemplate.query(sql, makeRowMapperInstance(), argsWithEndStart);
		} catch (EmptyResultDataAccessException e) {
			return new ArrayList();
		}
	}

	public List<Map<String, Object>> execSql(String sql, Object... args) {
		try {
			String argsStr = "";
			for (Object arg : args) {
				argsStr += arg.toString() + ", ";
			}
			log.debug(sql + "\t" + argsStr);
			return rJdbcTemplate.queryForList(sql, args);
		} catch (Exception e) {
			return new ArrayList<Map<String, Object>>();
		}
	}

	@SuppressWarnings("unchecked")
	public <T> T execSqlAsObject(String sql, Object... args) {
		try {
			String argsStr = "";
			for (Object arg : args) {
				argsStr += arg.toString() + ", ";
			}
			log.debug(sql + "\t" + argsStr);
			return (T) rJdbcTemplate.queryForObject(sql, makeRowMapperInstance(), args);
		} catch (Exception e) {
			return null;
		}
	}

}
